#!/usr/bin/env python
# -*- coding: utf-8 -*-

# import logging
# _logger = logging.getLogger()

# _logger.setLevel(logging.WARNING) 



from .tools import singleton

from sqlalchemy import  create_engine

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float

from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class TableIotValue(Base):
    __tablename__ = 'iot_value'
    id = Column(Integer, primary_key=True, autoincrement=True)
    code = Column(String(20))
    value = Column(Float)
    time = Column(String(19))
    etl_timestamp = Column(Integer)



class BaseModel(object):
    def __init__(self, db, table):
        self.db = db
        self.table = table

    def create(self, value_src):
        session = self.db.get_session()

        values = value_src

        if not isinstance ( value_src, list):
            values = [value_src]

        recs = [  self.table(**value) for value in values ]
        # rec = self.table(**value)
        # session.add(rec)
        session.add_all( recs )

        session.commit()
        return [ rec.id for rec in recs ]

    def search_unlink(self, domain ):
        session = self.db.get_session()
        res = session.query(self.table).filter_by(**domain).all()

        for rec in res:
            session.delete(rec)

        session.commit()
        return True


    def search(self, domain=None, fields=None ):
        session = self.db.get_session()

        if domain:
            res = session.query(self.table).filter_by(**domain).all()
        else:
            res = session.query(self.table).all()

        return [dict ( (fld, getattr(rec, fld) ) for fld in fields ) for rec in res ]


    def unlink(self, id):
        session = self.db.get_session()
        rec = session.query(self.table).filter_by(id=id).first()
        # print('unlink', rec)
        if rec:
            session.delete(rec)
            session.commit()
        return True

class IotValue(BaseModel):
    def __init__(self, db):
        super( IotValue, self ).__init__( db , TableIotValue)
    

    def search(self, domain=None):
        fields = ['id', 'code', 'value', 'time', 'etl_timestamp']
        return super( IotValue, self ).search(domain=domain, fields=fields)

    def update_or_create(self, values):
        # id 会重复使用  ret 的 ids 不准确, TBD
        ret = []
        for val in values:
            domain = {
                'code': val['code'],
                'etl_timestamp': val['etl_timestamp'],
            }
            self.search_unlink(domain)
            ret = ret + self.create(val)
        return ret

@singleton
class DB(object):
    def __init__(self, db_name):
        self.env = {
            'iot.value': IotValue(self )
        }
        self.db_name = db_name
        self.init_db()

    def init_db(self):
        conn_str = 'sqlite:///' + self.db_name + '?check_same_thread=False'
        # engine = create_engine(conn_str, echo=True)
        engine = create_engine(conn_str, echo=False)
        Base.metadata.create_all(engine, checkfirst=True)
        # IotValue.__table__.create(engine, checkfirst=True)

    def get_engine(self):
        conn_str = 'sqlite:///' + self.db_name + '?check_same_thread=False'
        engine = create_engine(conn_str, echo=False)

        return engine

    def get_session(self):
        engine = self.get_engine()
        Session = sessionmaker(bind=engine)
        session = Session()
        return session




def get_db(db_name):
    return DB(db_name)



    

# import sqlite3



# from functools import wraps

# def wrap_db(obj):
#     @wraps(obj)
#     def inner(self, *args, **kwargs):
#         # print('hello inner')
#         self.open()
#         ret = obj(self, *args, **kwargs)
#         self.close()
#         return ret
 
#     return inner

# class BaseDB(object):
#     def __init__(self, db):
#         self.db = db

#     def open(self):
#         conn=sqlite3.connect(self.db)
#         self.conn = conn
#         self.cr = conn.cursor()
#         print('open')

#     def close(self):
#         print('close')
          
#         self.cr.close()   
#         self.conn.close()

#     @wrap_db
#     def execute(self, sql):
#         print(' call  execute, sql: ', sql)
#         cr = self.cr
#         cr.execute(sql)
#         self.conn.commit()   
#         return None


#     @wrap_db
#     def fetchall(self, sql):
#         print(' call fetchall, sql: ', sql)
#         cr = self.cr
#         cr.execute(sql)
#         return cr.fetchall()



# class DB(BaseDB):
#     def get_config(self):
#         print( 'sql , get_config' )
#         sql = """
#             SELECT code, host, db, user, password, last_etl_time FROM  iot_device
#         """
#         data = self.fetchall(sql )
#         code, host, db, user, password, last_etl_time = data[0]
#         rec = {
#             'code': code,
#             'host': host, 
#             'db': db, 
#             'user': user, 
#             'password': password, 
#             'last_etl_time': last_etl_time
#         }
#         print('sql , get_config 2', rec)

#         return rec

#     def set_config(self, config):
#         print( 'sql , set_config', config )

#         host = config.get('host')
#         db = config.get('db')
#         user = config.get('user')
#         password = config.get('password')
#         code = config.get('code')
#         last_etl_time = '2000-01-01 00:00:00'

#         sql = """
#             INSERT INTO iot_device (
#                 code, host, db, user, password, last_etl_time
#             ) VALUES ( '%s', '%s', '%s', '%s', '%s', '%s' )
#             """ % ( code, host, db, user, password, last_etl_time )


#         ret = self.execute(sql )
#         print('sql , set_config 2', ret)
#         return ret

#     def create_table_device(self):
#         print( 'create_table_device',  )

#         sql = """
#             CREATE TABLE IF NOT EXISTS iot_device(
#                 code TEXT PRIMARY KEY,
#                 host TEXT,
#                 db TEXT,
#                 user TEXT,
#                 password TEXT,
#                 last_etl_time TEXT
#             )"""

#         ret = self.execute(sql )
#         print('create_table_device 2', ret)
#         return ret

#     @property
#     def is_init(self):
#         print('is_init call ')
#         sql = "select name from sqlite_master where type='table' order by name"
#         return self.fetchall(sql )

